﻿print 'Rollback v1.23'
go

print 'ALTER PROCEDURE  Rep1SocR3'
go

ALTER PROCEDURE dbo.Rep1SocR3
	(
		@D_S datetime,	
		@D_PO datetime,
		@K_1SOC int
	)
AS
-- Отчет «Отчет о работе с отдельными категориями семей» 
-- Раздел 3
BEGIN
	
	DECLARE @C_LUDI int
	DECLARE @C_ALL int
	
	-- 46 Отделения первичного приема, информации, анализа и прогназирования
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES (@K_1SOC, 46, 2, 0)
	
	-- 47		телефоны "горячей линии"
	SELECT @C_LUDI = count(K_KPO) FROM b_kpo WHERE F_KRIZIS = 1 AND D_OBRASCH Between @D_S And @D_PO
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 47, 1, @C_LUDI)
	
	-- 49       методические библиотеки
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES (@K_1SOC, 49, 0, -1)

	-- 62       Численноть гражнан, ослуживаемых отделениями социальной помощи на дому (сумма строк 063, 065, 066)
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI)VALUES (@K_1SOC, 62, -1, 0)
	
	-- 63 - 73 забанить C_ALL       
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 63, -1, 0)
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 64, -1, 0)
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 65, -1, 0)
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 66, -1, 0)
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 67, -1, 0)
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 68, -1, 0)
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 69, -1, 0)
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 70, -1, 0)
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 71, -1, 0)
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 72, -1, 0)
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 73, -1, 0)

	-- 74 и 75 забанить C_LUDI       
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 74, 0, -1)
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 75, 0, -1)

	-- 76 установть C_ALL = 2      
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 76, 2, 0)

	-- 78 установть C_ALL = 1      
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 78, 0, -1)

	-- 91 - 94 забанить C_LUDI       
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 91, 0, -1)
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 92, 0, -1)
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 93, 0, -1)
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 94, 0, -1)

	-- 95 Проведено юридический консультаций
	SELECT @C_ALL = Count(K_UKS) FROM s_ur_prich_obr INNER JOIN b_uks ON s_ur_prich_obr.K_PRICH = b_uks.K_PRICH_UR
	WHERE b_uks.D_DZ Between @D_S And @D_PO
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 95, @C_ALL, 0)

	-- 97 Реабаталитационные приюты
	SELECT @C_LUDI = Count(K_UKS) FROM b_uks_chelder
	WHERE b_uks_chelder.D_NACH Between @D_S And @D_PO AND b_uks_chelder.D_OKON Between @D_S And @D_PO
	 OR b_uks_chelder.D_NACH Is Null AND b_uks_chelder.D_OKON Between @D_S And @D_PO
	 OR b_uks_chelder.D_NACH Between @D_S And @D_PO AND b_uks_chelder.D_OKON Is Null
	INSERT INTO bo_1soc_r3_ (K_1SOC, K_ROW_NUM, C_ALL, C_LUDI) VALUES(@K_1SOC, 97, 1, @C_LUDI)

	-- Заполняем пустые строки
	INSERT INTO bo_1soc_r3_(K_1SOC, K_ROW_NUM, C_ALL, C_LUDI)
	SELECT @K_1SOC, so_1soc.K_ROW_NUM, 0, 0
	FROM so_1soc LEFT JOIN bo_1soc_r3_ ON so_1soc.K_ROW_NUM = bo_1soc_r3_.K_ROW_NUM AND bo_1soc_r3_.K_1SOC = @K_1SOC
	WHERE  so_1soc.K_RAZDEL = 3 and bo_1soc_r3_.K_ROW_NUM is null


RETURN
END